For the purposes of performing a fetch for a report, I was curious to know the XML syntax for extracting only part of a DateTime from a DateTime field. Say I have the following example fetch used to obtain a DateTime field "xxx_start": <fetch mapping="logical" version="1.0"> <entity name="serviceappointment"> <attribute name="xxx_start" /> </entity></fetch> How can I use XML to only fetch the day of "xxx_start", or only the month, or only the year, or only the time?

I found bits and pieces of the solution I need in the CRM 2011 SDK, although not a complete solution. Apparently, you can perform grouping with fetch XML, which can be done by the day, month, or year of a DateTime field--not the time (to my knowledge). However, this aggregate grouping approach outlined in the SDK seems to only work by returning "distinct" records. The example query below behaves as if I have specified "distinct", which I actually have not (I assume this is because aggregate="true"; it does not seem possible to have it set to false and group like I want.): <fetch mapping="logical" aggregate="true" version="1.0"> <entity name="serviceappointment"> <attribute name="xxx_start" groupby="true" dategrouping="year" alias="year"/> </entity> </fetch> One interesting thing about this query is, although it returns only the year part of what was originally a DateTime field, it returns this year value as a string. And not only that, but it returns it as a "formattedvalue" which injects punctuation. This is a problem in the case of querying year values because year "2011" becomes year "2,011". The result XML of the example fetch is posted below: <resultset morerecords="0"> <result> <year></year> </result> <result> <year formattedvalue="2,011">2011</year> </result> </resultset> How can this punctuation issue be resolved? In this particular case, returning the "unformattedvalue" (if that is the proper term) instead of the "formattedvalue" would work. Also, applying some type of string formatting would work, although I have not yet found a way to do that with fetch XML. My guess is it is beyond the power of fetch XML to do that.

Hi RST Dev, From your scenario, you would like to format date in XML, right? Please correct me if my understanding is wrong. If that’s the case, please refer to the following article, Custom Date/Time Formats: http://msdn.microsoft.com/en-us/library/ee254558(v=BTS.10).aspx Please understanding that as here we generally focus on the troubleshooting of reporting services issues and the problem we are encountering has beyond our specialty, I suggest you post the thread to our XML forum: http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/threads, there are many experts focus on XML and hope the issue could be resolved soon. Thanks, Eileen

Hi RST Dev, From your scenario, you would like to format date in XML, right? Please correct me if my understanding is wrong. If that’s the case, please refer to the following article, Custom Date/Time Formats: http://msdn.microsoft.com/en-us/library/ee254558(v=BTS.10).aspx Please understanding that as here we generally focus on the troubleshooting of reporting services issues and the problem we are encountering has beyond our specialty, I suggest you post the thread to our XML forum: http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/threads, there are many experts focus on XML and hope the issue could be resolved soon. Thanks, Eileen